package com.service;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.type.newTypeUser;

public class Db {
	Connection con;

	
	public Db() throws SQLException {
		con = DriverManager.getConnection("jdbc:derby:db;create=true");
	}

	
	public void dropTable() throws SQLException {
		Statement st = con.createStatement();
		st.executeUpdate("drop table USERS");
		st.close();
	}

	
	public void initialTable() throws SQLException {
//		dropTable();
	    DatabaseMetaData dmd = con.getMetaData();
	    
	    ResultSet rsUser = dmd.getTables(null, null, "USERS", null);
		if (rsUser.next()) {
			PreparedStatement pst = con.prepareStatement("update USERS set IS_ONLINE=?");
			pst.setBoolean(1, false);
			pst.execute();
			pst.close();
		} else {
			Statement st = con.createStatement();
			st.executeUpdate("create table USERS (PHONE_NUMBER BIGINT, PASSWORD VARCHAR(32), NAME VARCHAR(32), AVATAR BLOB, IS_ONLINE SMALLINT, PRIMARY KEY (PHONE_NUMBER))");
			st.close();
		}
		
		ResultSet rsTopic = dmd.getTables(null, null, "TOPIC", null);
		if (!rsTopic.next()) {
			Statement st = con.createStatement();
			st.executeUpdate("create table TOPIC (CREATOR_PHONE_NUM BIGINT, TITLE VARCHAR(32), CONTENT VARCHAR(32), PHOTO BLOB, FOLLOWER_PHONE_NUM BIGINT ARRAY[5])");
//			st.executeUpdate("create table REGIONS (REGION_NAME varchar(32) NOT NULL, ZIPS varchar32 ARRAY[10] NOT NULL, PRIMARY KEY (REGION_NAME));");
			
			st.close();
		}
	}

	
	public Boolean insertNewUser(long phoneNumber, String password, String name, byte[] avatarByteArray, Boolean isOnline) throws IOException, SQLException {
		if (isExistAccount(phoneNumber)) 
			return false;
		else {
			try {
				PreparedStatement pst = con.prepareStatement("insert into USERS values(?,?,?,?,?)");
				pst.setLong(1, phoneNumber);
				pst.setString(2, password);
				pst.setString(3, name);
				InputStream is = new ByteArrayInputStream(avatarByteArray);
				pst.setBlob(4, is);
				pst.setBoolean(5, isOnline);
				pst.execute();
				is.close();
				pst.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			return true;
		}
	}

	
	public Boolean isExistAccount(long phoneNumber) throws SQLException {
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery("select * from USERS where PHONE_NUMBER=" + Long.toString(phoneNumber));
		return rs.next();
	}
	
	
	public newTypeUser validateAccount(long phoneNumber, String password) throws SQLException {
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery("select * from USERS where PHONE_NUMBER=" + Long.toString(phoneNumber) + " and PASSWORD='" + password + "'");
		if(rs.next()) {
			// get self name and avatar
			newTypeUser dataPackage = new newTypeUser();
			dataPackage.setName(rs.getString(3));
			dataPackage.setAvatar(rs.getBytes(4));
			
			// indicate this account is online
			st.executeUpdate("update USERS set IS_ONLINE=1 where PHONE_NUMBER=" + Long.toString(phoneNumber));
			st.close();
			
			return dataPackage;
		} else
			return null;
	}


	public newTypeUser findFriend(long friendPhoneNum) throws SQLException {
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery("select * from USERS where PHONE_NUMBER=" + Long.toString(friendPhoneNum) + " and IS_ONLINE=1");
		if(rs.next()) {
			newTypeUser dataPackage = new newTypeUser();
			dataPackage.setName(rs.getString(3));
			dataPackage.setAvatar(rs.getBytes(4));
			return dataPackage;
		} else
			return null;
	}


	public Boolean receiveCreatedTopic(long phoneNum, String title, String content, byte[] byteArray) {
		/*try {
			PreparedStatement pst = con.prepareStatement(SrvVal.INSERT_NEW_USER);
			pst.setLong(1, phoneNumber);
			pst.setString(2, password);
			pst.setString(3, name);
			InputStream is = new ByteArrayInputStream(avatarByteArray);
			pst.setBlob(4, is);
			pst.setBoolean(5, isOnline);
			pst.execute();
			is.close();
			pst.close();
		} catch (Exception e) {
			e.printStackTrace();
		}*/
		return true;
	}
}